// src/main/java/com/ruoyi/chat/util/ExcelTimeRangeAnalyzer.java

package com.reliable.service.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.Date;

public class ExcelTimeRangeAnalyzer {

    public static TimeRange analyze(InputStream inputStream) throws Exception {
        LocalDateTime minTime = null;
        LocalDateTime maxTime = null;

        try (Workbook workbook = new XSSFWorkbook(inputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                Cell cell = row.getCell(3); // created_at 列
                if (cell == null) continue;

                LocalDateTime time = getLocalDateTime(cell);
                if (time != null) {
                    if (minTime == null || time.isBefore(minTime)) minTime = time;
                    if (maxTime == null || time.isAfter(maxTime)) maxTime = time;
                }
            }
        }

        if (minTime == null || maxTime == null) {
            throw new IllegalArgumentException("Excel 中未找到有效时间数据");
        }
        return new TimeRange(minTime, maxTime);
    }

    private static LocalDateTime getLocalDateTime(Cell cell) {
        if (cell == null) return null;
        if (cell.getCellType() == CellType.NUMERIC && DateUtil.isCellDateFormatted(cell)) {
            Date date = cell.getDateCellValue();
            return date.toInstant().atZone(ZoneId.of("Asia/Shanghai")).toLocalDateTime();
        } else if (cell.getCellType() == CellType.STRING) {
            try {
                return LocalDateTime.parse(cell.getStringCellValue().trim());
            } catch (Exception e) {
                return null;
            }
        }
        return null;
    }

    public static class TimeRange {
        public final LocalDateTime min;
        public final LocalDateTime max;
        public TimeRange(LocalDateTime min, LocalDateTime max) {
            this.min = min;
            this.max = max;
        }
    }
}